﻿CREATE TRIGGER [trd_Note]
ON [dbo].[t_Note]
FOR DELETE
AS

IF @@ROWCOUNT = 0 RETURN

DELETE N_L FROM t_Note_Last N_L INNER JOIN deleted d ON N_L.LoanID = d.LoanID --AND N_L.NoteTypeID = d.NoteTypeID

INSERT INTO t_Note_Last(LoanID,NoteID,NoteTypeID)
SELECT     N.LoanID, N.ID, N.NoteTypeID
FROM (
SELECT N.LoanID, N.NoteTypeID, MAX(N.NoteDate)NoteDate
FROM t_Note N INNER JOIN deleted d ON d.LoanID = N.LoanID-- AND d.NoteTypeID = N.NoteTypeID
WHERE N.NoteUser  IN (SELECT DISTINCT sAMAccountName FROM v_ADS_Collector) AND N.IsResolved = 1
GROUP BY N.LoanID, N.NoteTypeID
) AS NL INNER JOIN t_Note AS N ON NL.LoanID = N.LoanID AND NL.NoteTypeID = N.NoteTypeID AND NL.NoteDate = N.NoteDate

